package tcm.com.gistone.api.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import tcm.com.gistone.database.config.GetBySqlMapper;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by wangfan on 2017/8/29.
 */
@Service
public class WordService {

    @Autowired
    private GetBySqlMapper getBySqlMapper;



    public  List<Map> getStatisticsByWord(Integer specialId,long queryWordId){
        String sql;
        if(null==specialId){
            sql = "select a.book_id,b.book_name,count(a.section_id) as number from tb_ws_relation_copy a INNER JOIN tb_bookinfo b  where a.book_id=b.book_id  and a.word_id ="+queryWordId+" GROUP BY  a.book_id order by number desc";
        }else{
            sql = "select a.book_id,b.book_name,count(a.section_id) as number from tb_ws_relation_copy a INNER JOIN tb_bookinfo b  where a.book_id=b.book_id and a.book_id in (SELECT book_id FROM  tb_bookinfo WHERE  special_id ="+specialId+") and a.word_id ="+queryWordId+" GROUP BY  a.book_id order by number desc";
        }
        return getBySqlMapper.findRecords(sql);
    }


    public  List<Map> getStatisticsByWord2(Integer specialId,String  word){
        String sql="select b.book_id,count(s.section_id) as number,b.book_name from tb_section s INNER  JOIN  tb_bookinfo b ON  s.book_id=b.book_id where 1=1 ";
        if(null!=specialId){
            sql = sql+"and b.special_id="+specialId;
        }
        sql=sql+" and locate(\""+word+"\" , s.section_content) > 0 and b.status =7 GROUP BY  b.book_id ORDER BY number desc";
        return getBySqlMapper.findRecords(sql);
    }

    public  List<Map> getStatisticsByMutiWord(Integer specialId,long queryWordId1,long queryWordId2){

        String sql =
                " select a.book_id,b.book_name,count(a.section_id) as number from" +
                "  (select t1.book_id as book_id,t1.section_id as section_id from " +
                        "(SELECT  * from tb_ws_relation_copy WHERE word_id="+queryWordId1+") t1" +
                " inner JOIN " +
                " (SELECT  * from tb_ws_relation_copy  WHERE word_id="+queryWordId2+" ) t2 " +
                " on t1.section_id=t2.section_id) a INNER JOIN tb_bookinfo b  " +
                        "on a.book_id=b.book_id " ;

        if(specialId!=null){
            sql =sql+"and a.book_id in (SELECT book_id FROM  tb_bookinfo WHERE  special_id ="+specialId+")";
        }

        sql =sql+ " GROUP BY  a.book_id order by number desc;";


        return getBySqlMapper.findRecords(sql);
    }

    public  List<Map> getStatisticsByMutiWord2(Integer specialId,String word1,String word2){

        String sql="select b.book_id,count(s.section_id) as number,b.book_name from tb_section s INNER  JOIN  tb_bookinfo b ON  s.book_id=b.book_id where 1=1 ";
        if(null!=specialId){
            sql = sql+"and b.special_id="+specialId;
        }
        sql=sql+" and locate(\""+word1+"\" , s.section_content) > 0 and locate(\""+word2+"\" , s.section_content) > 0  and b.status =7 GROUP BY  b.book_id ORDER BY number desc";
        return getBySqlMapper.findRecords(sql);
    }

    public  int getNumberByThreeWord(Integer specialId,long wordId ,long queryWordId1,long queryWordId2){

        String sql =
                " SELECT count(*) as number " +
                        " FROM (tb_ws_relation_copy a INNER JOIN tb_ws_relation_copy b ON a.section_id=b.section_id) inner join tb_ws_relation_copy c on a.section_id=c.section_id  and b.word_id ="+wordId+"  and a.word_id ="+queryWordId1+"  and c.word_id ="+queryWordId2+"  ";
        if(specialId!=null){
            sql =sql+" and a.book_id in (SELECT book_id FROM  tb_bookinfo WHERE  special_id ="+specialId+")";
        }



        return getBySqlMapper.findrows(sql);
    }

    public  int getNumberByWordId(Integer specialId,long wordId ){

        String sql =
                " SELECT count(*) as number " +
                        "FROM tb_ws_relation_copy b where b.word_id ="+wordId;
        if(specialId!=null){
            sql =sql+" and b.book_id in (SELECT book_id FROM  tb_bookinfo WHERE  special_id ="+specialId+")";
        }


        return getBySqlMapper.findrows(sql);
    }

    public List<Map> getListByWordAndBookId(String word,long bookId){

        String sql = "select * from tb_section where book_id =" + bookId + " and section_content like '%" + word + "%' ORDER BY CASE  WHEN section_title LIKE  '%" + word + "%' THEN  section_title ELSE  section_content END DESC ";


        List<Map> result = getBySqlMapper.findRecords(sql);

        List<Map> list = new ArrayList<>();

        if (result.size() > 0) {
            for (int i = 0; i < result.size(); i++) {
                Map item = result.get(i);
//                    String sectionTitle = item.get("section_title").toString().replaceAll(word, "<span class='titleSelective'>" + word + "</span>");
                String sectionTitle = item.get("section_title").toString();
//                    String sectionContent = item.get("section_content").toString().replaceAll(word, "<span class='selective'>" + word + "</span>");
                String sectionContent = item.get("section_content").toString();


                String sectionId = item.get("section_id").toString();

                String pagenum = item.get("number").toString();


                Map m = new HashMap();
                m.put("sectionTitle", sectionTitle);
                m.put("sectionContent", sectionContent);
                m.put("sectionId", sectionId);
                m.put("pagenum", pagenum);

                list.add(m);
            }
        }
        return  list;
    }


    public Map  queryPlaceByBookAndWord(long bookId,String word){

        Map m = new HashMap();
        String str="";
        String sql =  "select * from tb_knowledge,tb_word where tb_knowledge.knowledge_name =tb_word.word  and theme_type =9 and book_id =" + bookId + " and tb_knowledge.knowledge_name='"+word+"'";

        List<Map> result = getBySqlMapper.findRecords(sql);

        if(result.size()>0){

            if(null!=result.get(0).get("genuine")) {

                str = result.get(0).get("genuine").toString();
                if (!str.isEmpty()) {
                    sql = "select * from tb_ancient_modern_place where ancient ='" + str + "' and name='" + word + "'";
                    result = getBySqlMapper.findRecords(sql);
                    if (result.size() > 0) {
                        if(null!=result.get(0).get("modern")) {
//                            str = result.get(0).get("modern").toString();
//                            m.put("modern",result.get(0).get("modern").toString());
//                            m.put("ancient",result.get(0).get("ancient").toString());
//                            m.put("name",result.get(0).get("name").toString());
                            return  result.get(0);
                        }


                    }

                }
            }
        }

        return  m;
    }
    public  long getWordIdByWordString(Integer specialId,String word){

        String sql;
        System.out.println(specialId);
        if(null==specialId){
            sql = "select * from tb_word where  word ='"+word+"'";

        }else{
            sql = "select * from tb_word where special_id="+specialId+" and word ='"+word+"'";

        }
        List<Map> result = getBySqlMapper.findRecords(sql);

        long queryWordId;
        if(result.size()>0) {
            Map wordResult = result.get(0);
            Long parentId = (Long) wordResult.get("parent_id");
            Long wordId = (Long) wordResult.get("word_id");
            if (!parentId.equals(wordId)) {
                //word存在父亲节点
                sql = "select * from tb_word where word_id=" + parentId;
                result = getBySqlMapper.findRecords(sql);
                queryWordId = (Long) result.get(0).get("word_id");

            } else {
                queryWordId = wordId;
            }
            return  queryWordId;
        }else{
            return 0;
        }

    }
    public  long getWordTypeByID(Long wordId) {

        String sql = "select * from tb_word where  word_id ="+wordId;
        List<Map> result = getBySqlMapper.findRecords(sql);

        if(result.size()>0){
            return  (long)result.get(0).get("theme_type");
        }else{
            return  -1;
        }
    }

    public  String getWordThemeById(Long themeId) {

        String sql = "select * from tb_theme  where theme_id ="+themeId;
        List<Map> result = getBySqlMapper.findRecords(sql);

        if(result.size()>0){
            return  result.get(0).get("theme_name").toString();
        }else{
            return  null;
        }
    }

    public  Map getWordTheme(Long wordId,Integer specailId) {

        String sql = "select * from tb_word,tb_theme where tb_word.theme_type =tb_theme.theme_id and word_id ="+wordId;

        if(null!=specailId){
            sql =sql+" and tb_theme.special_id ="+specailId;
        }
        List<Map> result = getBySqlMapper.findRecords(sql);

        if(result.size()>0){
            return  result.get(0);
        }else{
            return  null;
        }
    }


    }
